/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seagate.app.report;

import com.seagate.app.bean.SliderBean;
import com.seagate.app.manager.SliderManager;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 *
 * @author 452530
 */
public class IncommingBinExcel {

    public HSSFWorkbook IncommingBinExcelReport() {
        String DATE_FORMAT = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        Calendar c1 = Calendar.getInstance(); // today
        // response.setHeader("Content-Disposition", "attachment; filename=hgareport-"+sdf.format(c1.getTime())+".xls");
        //  OutputStream out = response.getOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            SliderManager pc = new SliderManager();
            // Create a workbook

            // Create a sheet
            HSSFSheet[] sheet = new HSSFSheet[4];
            // Set style

            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.RED.index, (byte) 252, (byte) 157, (byte) 112);

            palette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 255, (byte) 255, (byte) 66);

            HSSFCellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            HSSFFont f2 = wb.createFont();
            //set font 2 to 10 point type
            f2.setFontHeightInPoints((short) 10);
            //make it red
            f2.setColor((short) 0);
            //make it bold
            f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            style.setFont(f2);

            HSSFCellStyle styleYello = wb.createCellStyle();
            styleYello.setFillForegroundColor(HSSFColor.YELLOW.index);
            styleYello.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleYello.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle styleRed = wb.createCellStyle();
            styleRed.setFillForegroundColor(HSSFColor.RED.index);
            styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle normal = wb.createCellStyle();
            normal.setFillForegroundColor(HSSFColor.WHITE.index);
            normal.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
            normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
            normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            
            HSSFCellStyle cs = wb.createCellStyle();
            HSSFDataFormat df = wb.createDataFormat();
            cs.setDataFormat(df.getFormat("d-mmm-yy h:mm"));


            String[][] productNameTab = {
                {"TDT", "'TDT'"},
                {"TST", "'TST'"},
                {"RTD&TDM", "'RTD','TDM'"}
            };

            for (int ln = 0; ln < productNameTab.length; ln++) {


                if (true) {
                    // Create Row[TDT]
                    HSSFRow row = null;
                    sheet[ln] = wb.createSheet(productNameTab[ln][0]);
                    //sheet[ln].setColumnGroupCollapsed((short) 1, true) ;
                    sheet[ln].groupColumn((short) 10, (short) 14);
                    sheet[ln].groupColumn((short) 19, (short) 21);
                    sheet[ln].groupColumn((short) 23, (short) 24);
                    //               row = sheet[ln].createRow((short) 0);


                    // Create cells (cell[rowindex++] - CELL[n])and set style for Row[0]
                    HSSFCell cell[] = new HSSFCell[28];
                    int indexData = 0;

                    List binList = pc.loadIncomingBinByProduct(productNameTab[ln][1]);
                    int countdata=0;
                    for (int i = 0; i < binList.size(); i++) {
                        List sliderList = pc.loadSilderByBinAllSliderBo("" + binList.get(i));
                        if (sliderList.size() > 0) {
                            countdata=countdata+sliderList.size();
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");









                            indexData++;
                        }
                        for (int v = 0; v < sliderList.size(); v++) {

                            SliderBean sb = (SliderBean) sliderList.get(v);
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                double gap = sb.getSlider_contribution();
                                if (gap >= 2 && gap <= 3) {
                                    cell[indexCell].setCellStyle(styleYello);
                                } else if (gap > 3) {
                                    cell[indexCell].setCellStyle(styleRed);
                                } else {
                                    cell[indexCell].setCellStyle(normal);
                                }
                            }
                            // Set value of cells for Row[1] - Row[n]
                            int rowindex = 0;
                            cell[rowindex++].setCellValue(sb.getBin());
                            cell[rowindex++].setCellValue(sb.getProductName());
                            cell[rowindex++].setCellValue(sb.getTsr());

                            cell[rowindex++].setCellValue(sb.getSld_cmp_qty());
                            cell[rowindex++].setCellValue(sb.getSeq());
                            cell[rowindex++].setCellValue(sb.getWaf_lot());
                            cell[rowindex++].setCellValue(sb.getPriority());
                            cell[rowindex++].setCellValue(sb.getHga_bo());

                            cell[rowindex++].setCellValue(sb.getRetest_bo());

                            int tempcell = rowindex++;
                            if ("Withdrawn".equalsIgnoreCase(sb.getHga_status()) || "Inactive".equalsIgnoreCase(sb.getHga_status())) {

                                cell[tempcell].setCellValue("Cancel");
                            } else {
                                if (sb.getHga_cmp_date() != null) {
                                    cell[tempcell].setCellValue("Complete");
                                    if ("FB".equalsIgnoreCase(sb.getBuildgroup())) {
                                        cell[tempcell].setCellValue("Weekly Rebuild");
                                    }
                                } else {
                                    if (sb.getHga_bo() != null && sb.getHga_bo().startsWith("TH") && "STTH".equalsIgnoreCase(sb.getLocation()) && sb.getTsr() != null) {
                                        cell[tempcell].setCellValue("Ready");
                                    } else {
                                        cell[tempcell].setCellValue("Not Ready");
                                    }
                                }
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getWaf_ship_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getWaf_ship_date_2());
                            }

                            cell[rowindex++].setCellValue(sb.getSld_material_form());
                            cell[rowindex++].setCellValue(sb.getSld_bo_priority());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSld_bo_first_approval_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSld_bo_first_approval_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSlider_start_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSlider_start_date_2());
                            }



                            cell[rowindex++].setCellValue(""+sb.getWafgap());
                            cell[rowindex++].setCellValue(sb.getMaterial_form());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getForcast_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getForcast_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getShip_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getShip_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSlider_ship_destination());
                            
                            if (sb.getReceive_date() != null || "".equalsIgnoreCase(sb.getReceive_date())) {
                                cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                                cell[rowindex++].setCellValue(sb.getReceive_date_2());

                            } else {
                                cell[rowindex++].setCellValue("wait sld");
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getDoc_ready_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getDoc_ready_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSld_gap());
                            cell[rowindex++].setCellValue(""+sb.getSlider_contribution());

                            cell[rowindex++].setCellValue(""+sb.getDocDelay());

                            cell[rowindex++].setCellValue(sb.getLocation());



                            //cell[rowindex++].setCellValue(sb.getHga_act_issue_dt());
                            cell[rowindex++].setCellValue(sb.getHgaqueue());










                            indexData++;
                        }//end while
                        if (sliderList.size() > 0) {
                            indexData = indexData + 2;
                        }
                        
                    }
                     if (countdata == 0) {
                            
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");









                            indexData++;
                        }
                }
            }


        } finally {
            //   out.close();
        }

        return wb;
    }

    public HSSFWorkbook BinExcelReport(String binId) {
        String DATE_FORMAT = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        Calendar c1 = Calendar.getInstance(); // today
        // response.setHeader("Content-Disposition", "attachment; filename=hgareport-"+sdf.format(c1.getTime())+".xls");
        //  OutputStream out = response.getOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            SliderManager pc = new SliderManager();
            // Create a workbook

            // Create a sheet
            HSSFSheet[] sheet = new HSSFSheet[4];
            // Set style

            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.RED.index, (byte) 252, (byte) 157, (byte) 112);

            palette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 255, (byte) 255, (byte) 66);

            HSSFCellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            HSSFFont f2 = wb.createFont();
            //set font 2 to 10 point type
            f2.setFontHeightInPoints((short) 10);
            //make it red
            f2.setColor((short) 0);
            //make it bold
            f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            style.setFont(f2);

            HSSFCellStyle styleYello = wb.createCellStyle();
            styleYello.setFillForegroundColor(HSSFColor.YELLOW.index);
            styleYello.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleYello.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle styleRed = wb.createCellStyle();
            styleRed.setFillForegroundColor(HSSFColor.RED.index);
            styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle normal = wb.createCellStyle();
            normal.setFillForegroundColor(HSSFColor.WHITE.index);
            normal.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
            normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
            normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);


            HSSFCellStyle cs = wb.createCellStyle();
            HSSFDataFormat df = wb.createDataFormat();
            cs.setDataFormat(df.getFormat("d-mmm-yy h:mm"));




            if (true) {
                // Create Row[TDT]
                HSSFRow row = null;
                sheet[0] = wb.createSheet("BIN Report");
                sheet[0].groupColumn((short) 10, (short) 14);
                sheet[0].groupColumn((short) 19, (short) 21);
                sheet[0].groupColumn((short) 23, (short) 24);

                //               row = sheet[ln].createRow((short) 0);


                // Create cells (cell[rowindex++] - CELL[n])and set style for Row[0]
                HSSFCell cell[] = new HSSFCell[28];



                SliderManager hga = new SliderManager();
                int indexData = 0;


                List sliderList = pc.loadSilderByBinAllSliderBo("" + binId);

                row = sheet[0].createRow((short) indexData);
                //sheet[ln].groupColumn((short)4,(short)5);
                for (int indexCell = 0; indexCell < 28; indexCell++) {
                    cell[indexCell] = row.createCell((short) indexCell);
                    cell[indexCell].setCellStyle(style);
                    sheet[0].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                }
                /* Set value of cells for Row[0] */ int rowindex = 0;
                cell[rowindex++].setCellValue("EXP ID");
                cell[rowindex++].setCellValue("PRODUCT");
                cell[rowindex++].setCellValue("ET TSR");
                cell[rowindex++].setCellValue("QTY");
                cell[rowindex++].setCellValue("Slider Seq.");
                cell[rowindex++].setCellValue("Wafer LOT");
                cell[rowindex++].setCellValue("Priority");
                cell[rowindex++].setCellValue("HGA BO");

                cell[rowindex++].setCellValue("Retest Bo");
                cell[rowindex++].setCellValue("Status");


                cell[rowindex++].setCellValue("Wafer Ship Date");
                cell[rowindex++].setCellValue("Sld Material Form");
                cell[rowindex++].setCellValue("Sld Bo Priority");
                cell[rowindex++].setCellValue("Slider Document Ready Date");
                cell[rowindex++].setCellValue("Sld Start Date");
                cell[rowindex++].setCellValue("Wafer partner gap (day)");

                cell[rowindex++].setCellValue("HGA Material Form");





                cell[rowindex++].setCellValue("Forecast Ship Date");
                cell[rowindex++].setCellValue("Slider Ship Date");
                cell[rowindex++].setCellValue("Slider Ship Destination");
                cell[rowindex++].setCellValue("Slider Receive");



                cell[rowindex++].setCellValue("Document Ready Date");
                cell[rowindex++].setCellValue("Sld partner gap(day)");
                cell[rowindex++].setCellValue("Sld Contribution(day)");
                cell[rowindex++].setCellValue("Document Delay");
                cell[rowindex++].setCellValue("Location");

                // cell[rowindex++].setCellValue("Date To Wip");
                cell[rowindex++].setCellValue("HGA Queue");
                indexData++;

                for (int v = 0; v < sliderList.size(); v++) {

                    SliderBean sb = (SliderBean) sliderList.get(v);
                    row = sheet[0].createRow((short) indexData);
                    for (int indexCell = 0; indexCell < 28; indexCell++) {
                        cell[indexCell] = row.createCell((short) indexCell);
                        double gap = sb.getSlider_contribution();
                        if (gap >= 2 && gap <= 3) {
                            cell[indexCell].setCellStyle(styleYello);
                        } else if (gap > 3) {
                            cell[indexCell].setCellStyle(styleRed);
                        } else {
                            cell[indexCell].setCellStyle(normal);
                        }
                    }
                    // Set value of cells for Row[1] - Row[n]
                    rowindex = 0;
                    cell[rowindex++].setCellValue(sb.getBin());
                    cell[rowindex++].setCellValue(sb.getProductName());
                    cell[rowindex++].setCellValue(sb.getTsr());

                    cell[rowindex++].setCellValue(sb.getSld_cmp_qty());
                    cell[rowindex++].setCellValue(sb.getSeq());
                    cell[rowindex++].setCellValue(sb.getWaf_lot());
                    cell[rowindex++].setCellValue(sb.getPriority());
                    cell[rowindex++].setCellValue(sb.getHga_bo());

                    cell[rowindex++].setCellValue(sb.getRetest_bo());

                    int tempcell = rowindex++;
                    if ("Withdrawn".equalsIgnoreCase(sb.getHga_status()) || "Inactive".equalsIgnoreCase(sb.getHga_status())) {

                        cell[tempcell].setCellValue("Cancel");
                    } else {
                        if (sb.getHga_cmp_date() != null) {
                            cell[tempcell].setCellValue("Complete");
                            if ("FB".equalsIgnoreCase(sb.getBuildgroup())) {
                                cell[tempcell].setCellValue("Weekly Rebuild");
                            }
                        } else {
                            if (sb.getHga_bo() != null && sb.getHga_bo().startsWith("TH") && "STTH".equalsIgnoreCase(sb.getLocation()) && sb.getTsr() != null) {
                                cell[tempcell].setCellValue("Ready");
                            } else {
                                cell[tempcell].setCellValue("Not Ready");
                            }
                        }
                    }

                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getWaf_ship_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getWaf_ship_date_2());
                            }

                            cell[rowindex++].setCellValue(sb.getSld_material_form());
                            cell[rowindex++].setCellValue(sb.getSld_bo_priority());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSld_bo_first_approval_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSld_bo_first_approval_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSlider_start_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSlider_start_date_2());
                            }



                            cell[rowindex++].setCellValue(""+sb.getWafgap());
                            cell[rowindex++].setCellValue(sb.getMaterial_form());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getForcast_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getForcast_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getShip_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getShip_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSlider_ship_destination());
                            
                            if (sb.getReceive_date() != null || "".equalsIgnoreCase(sb.getReceive_date())) {
                                cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                                cell[rowindex++].setCellValue(sb.getReceive_date_2());

                            } else {
                                cell[rowindex++].setCellValue("wait sld");
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getDoc_ready_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getDoc_ready_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSld_gap());
                            cell[rowindex++].setCellValue(""+sb.getSlider_contribution());

                            cell[rowindex++].setCellValue(""+sb.getDocDelay());

                            cell[rowindex++].setCellValue(sb.getLocation());



                            //cell[rowindex++].setCellValue(sb.getHga_act_issue_dt());
                            cell[rowindex++].setCellValue(sb.getHgaqueue());





                    indexData++;
                }//end while
                if (sliderList.size() > 0) {
                    indexData = indexData + 2;
                }
            }







        } finally {
            //   out.close();
        }

        return wb;
    }

    public HSSFWorkbook IncommingBinExcelReportNotCmp() {
        String DATE_FORMAT = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        Calendar c1 = Calendar.getInstance(); // today
        // response.setHeader("Content-Disposition", "attachment; filename=hgareport-"+sdf.format(c1.getTime())+".xls");
        //  OutputStream out = response.getOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            SliderManager pc = new SliderManager();
            // Create a workbook

            // Create a sheet
            HSSFSheet[] sheet = new HSSFSheet[4];
            // Set style

            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.RED.index, (byte) 252, (byte) 157, (byte) 112);

            palette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 255, (byte) 255, (byte) 66);

            HSSFCellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            HSSFFont f2 = wb.createFont();
            //set font 2 to 10 point type
            f2.setFontHeightInPoints((short) 10);
            //make it red
            f2.setColor((short) 0);
            //make it bold
            f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            style.setFont(f2);

            HSSFCellStyle styleYello = wb.createCellStyle();
            styleYello.setFillForegroundColor(HSSFColor.YELLOW.index);
            styleYello.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleYello.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle styleRed = wb.createCellStyle();
            styleRed.setFillForegroundColor(HSSFColor.RED.index);
            styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle normal = wb.createCellStyle();
            normal.setFillForegroundColor(HSSFColor.WHITE.index);
            normal.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
            normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
            normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle cs = wb.createCellStyle();
            HSSFDataFormat df = wb.createDataFormat();
            cs.setDataFormat(df.getFormat("d-mmm-yy h:mm"));



            String[][] productNameTab = {
                {"TDT", "'TDT'"},
                {"TST", "'TST'"},
                {"RTD&TDM", "'RTD','TDM'"}
            };

            for (int ln = 0; ln < productNameTab.length; ln++) {

                if (true) {
                    // Create Row[TDT]
                    HSSFRow row = null;
                    sheet[ln] = wb.createSheet(productNameTab[ln][0]);
                    //sheet[ln].setColumnGroupCollapsed((short) 1, true) ;
                    sheet[ln].groupColumn((short) 10, (short) 14);
                    sheet[ln].groupColumn((short) 19, (short) 21);
                    sheet[ln].groupColumn((short) 23, (short) 24);

                    //               row = sheet[ln].createRow((short) 0);


                    // Create cells (cell[rowindex++] - CELL[n])and set style for Row[0]
                    HSSFCell cell[] = new HSSFCell[28];



                    SliderManager hga = new SliderManager();
                    int indexData = 0;
                    int countdata=0;
                    List binList = pc.loadIncomingBinByProduct(productNameTab[ln][1]);
                    for (int i = 0; i < binList.size(); i++) {
                        List sliderList = pc.loadSilderByBin("" + binList.get(i));
                        if (sliderList.size() > 0) {
                            countdata=countdata+sliderList.size();
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");
                            indexData++;
                        }
                        for (int v = 0; v < sliderList.size(); v++) {

                            SliderBean sb = (SliderBean) sliderList.get(v);
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                double gap = sb.getSlider_contribution();
                                if (gap >= 2 && gap <= 3) {
                                    cell[indexCell].setCellStyle(styleYello);
                                } else if (gap > 3) {
                                    cell[indexCell].setCellStyle(styleRed);
                                } else {
                                    cell[indexCell].setCellStyle(normal);
                                }
                            }
                            // Set value of cells for Row[1] - Row[n]
                            int rowindex = 0;
                            cell[rowindex++].setCellValue(sb.getBin());
                            cell[rowindex++].setCellValue(sb.getProductName());
                            cell[rowindex++].setCellValue(sb.getTsr());

                            cell[rowindex++].setCellValue(sb.getSld_cmp_qty());
                            cell[rowindex++].setCellValue(sb.getSeq());
                            cell[rowindex++].setCellValue(sb.getWaf_lot());
                            cell[rowindex++].setCellValue(sb.getPriority());
                            cell[rowindex++].setCellValue(sb.getHga_bo());

                            cell[rowindex++].setCellValue(sb.getRetest_bo());

                            int tempcell = rowindex++;
                            if ("Withdrawn".equalsIgnoreCase(sb.getHga_status()) || "Inactive".equalsIgnoreCase(sb.getHga_status())) {

                                cell[tempcell].setCellValue("Cancel");
                            } else {
                                if (sb.getHga_cmp_date() != null) {
                                    cell[tempcell].setCellValue("Complete");
                                    if ("FB".equalsIgnoreCase(sb.getBuildgroup())) {
                                        cell[tempcell].setCellValue("Weekly Rebuild");
                                    }
                                } else {
                                    if (sb.getHga_bo() != null && sb.getHga_bo().startsWith("TH") && "STTH".equalsIgnoreCase(sb.getLocation()) && sb.getTsr() != null) {
                                        cell[tempcell].setCellValue("Ready");
                                    } else {
                                        cell[tempcell].setCellValue("Not Ready");
                                    }
                                }
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getWaf_ship_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getWaf_ship_date_2());
                            }

                            cell[rowindex++].setCellValue(sb.getSld_material_form());
                            cell[rowindex++].setCellValue(sb.getSld_bo_priority());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSld_bo_first_approval_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSld_bo_first_approval_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSlider_start_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSlider_start_date_2());
                            }



                            cell[rowindex++].setCellValue(""+sb.getWafgap());
                            cell[rowindex++].setCellValue(sb.getMaterial_form());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getForcast_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getForcast_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getShip_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getShip_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSlider_ship_destination());
                            
                            if (sb.getReceive_date() != null || "".equalsIgnoreCase(sb.getReceive_date())) {
                                cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                                cell[rowindex++].setCellValue(sb.getReceive_date_2());

                            } else {
                                cell[rowindex++].setCellValue("wait sld");
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getDoc_ready_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getDoc_ready_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSld_gap());
                            cell[rowindex++].setCellValue(""+sb.getSlider_contribution());

                            cell[rowindex++].setCellValue(""+sb.getDocDelay());

                            cell[rowindex++].setCellValue(sb.getLocation());



                            //cell[rowindex++].setCellValue(sb.getHga_act_issue_dt());
                            cell[rowindex++].setCellValue(sb.getHgaqueue());





                            indexData++;
                        }//end while
                        if (sliderList.size() > 0) {
                            indexData = indexData + 2;
                        }
                    }
                     if (countdata == 0) {
                            
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");









                            indexData++;
                        }
                }
            }



        } finally {
            //   out.close();
        }

        return wb;
    }

    public HSSFWorkbook ForcastBinExcelReportNotCmp() {
        String DATE_FORMAT = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        Calendar c1 = Calendar.getInstance(); // today
        // response.setHeader("Content-Disposition", "attachment; filename=hgareport-"+sdf.format(c1.getTime())+".xls");
        //  OutputStream out = response.getOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            SliderManager pc = new SliderManager();
            // Create a workbook

            // Create a sheet
            HSSFSheet[] sheet = new HSSFSheet[4];
            // Set style

            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.RED.index, (byte) 252, (byte) 157, (byte) 112);

            palette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 255, (byte) 255, (byte) 66);

            HSSFCellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            HSSFFont f2 = wb.createFont();
            //set font 2 to 10 point type
            f2.setFontHeightInPoints((short) 10);
            //make it red
            f2.setColor((short) 0);
            //make it bold
            f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            style.setFont(f2);

            HSSFCellStyle styleYello = wb.createCellStyle();
            styleYello.setFillForegroundColor(HSSFColor.YELLOW.index);
            styleYello.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleYello.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle styleRed = wb.createCellStyle();
            styleRed.setFillForegroundColor(HSSFColor.RED.index);
            styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle normal = wb.createCellStyle();
            normal.setFillForegroundColor(HSSFColor.WHITE.index);
            normal.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
            normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
            normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);


            HSSFCellStyle cs = wb.createCellStyle();
            HSSFDataFormat df = wb.createDataFormat();
            cs.setDataFormat(df.getFormat("d-mmm-yy h:mm"));



            String[][] productNameTab = {
                {"TDT", "'TDT'"},
                {"TST", "'TST'"},
                {"RTD&TDM", "'RTD','TDM'"}
            };

            for (int ln = 0; ln < productNameTab.length; ln++) {
                if (true) {
                    // Create Row[TDT]
                    HSSFRow row = null;
                    sheet[ln] = wb.createSheet(productNameTab[ln][0]);
                    //sheet[ln].setColumnGroupCollapsed((short) 1, true) ;
                    sheet[ln].groupColumn((short) 10, (short) 14);
                    sheet[ln].groupColumn((short) 19, (short) 21);
                    sheet[ln].groupColumn((short) 23, (short) 24);

                    //               row = sheet[ln].createRow((short) 0);


                    // Create cells (cell[rowindex++] - CELL[n])and set style for Row[0]
                    HSSFCell cell[] = new HSSFCell[28];



                    SliderManager hga = new SliderManager();
                    int indexData = 0;

                    List binList = pc.loadForecastBinByProduct(productNameTab[ln][1]);
                    int countdata=0;
                    for (int i = 0; i < binList.size(); i++) {
                        List sliderList = pc.loadSilderByBin("" + binList.get(i));
                        if (sliderList.size() > 0) {
                            countdata=countdata+sliderList.size();
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");
                            indexData++;
                        }
                        for (int v = 0; v < sliderList.size(); v++) {

                            SliderBean sb = (SliderBean) sliderList.get(v);
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                double gap = sb.getSlider_contribution();
                                if (gap >= 2 && gap <= 3) {
                                    cell[indexCell].setCellStyle(styleYello);
                                } else if (gap > 3) {
                                    cell[indexCell].setCellStyle(styleRed);
                                } else {
                                    cell[indexCell].setCellStyle(normal);
                                }
                            }
                            // Set value of cells for Row[1] - Row[n]
                            int rowindex = 0;
                            cell[rowindex++].setCellValue(sb.getBin());
                            cell[rowindex++].setCellValue(sb.getProductName());
                            cell[rowindex++].setCellValue(sb.getTsr());

                            cell[rowindex++].setCellValue(sb.getSld_cmp_qty());
                            cell[rowindex++].setCellValue(sb.getSeq());
                            cell[rowindex++].setCellValue(sb.getWaf_lot());
                            cell[rowindex++].setCellValue(sb.getPriority());
                            cell[rowindex++].setCellValue(sb.getHga_bo());

                            cell[rowindex++].setCellValue(sb.getRetest_bo());

                            int tempcell = rowindex++;
                            if ("Withdrawn".equalsIgnoreCase(sb.getHga_status()) || "Inactive".equalsIgnoreCase(sb.getHga_status())) {

                                cell[tempcell].setCellValue("Cancel");
                            } else {
                                if (sb.getHga_cmp_date() != null) {
                                    cell[tempcell].setCellValue("Complete");
                                    if ("FB".equalsIgnoreCase(sb.getBuildgroup())) {
                                        cell[tempcell].setCellValue("Weekly Rebuild");
                                    }
                                } else {
                                    if (sb.getHga_bo() != null && sb.getHga_bo().startsWith("TH") && "STTH".equalsIgnoreCase(sb.getLocation()) && sb.getTsr() != null) {
                                        cell[tempcell].setCellValue("Ready");
                                    } else {
                                        cell[tempcell].setCellValue("Not Ready");
                                    }
                                }
                            }

                             cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getWaf_ship_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getWaf_ship_date_2());
                            }

                            cell[rowindex++].setCellValue(sb.getSld_material_form());
                            cell[rowindex++].setCellValue(sb.getSld_bo_priority());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSld_bo_first_approval_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSld_bo_first_approval_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSlider_start_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSlider_start_date_2());
                            }



                            cell[rowindex++].setCellValue(""+sb.getWafgap());
                            cell[rowindex++].setCellValue(sb.getMaterial_form());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getForcast_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getForcast_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getShip_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getShip_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSlider_ship_destination());
                            
                            if (sb.getReceive_date() != null || "".equalsIgnoreCase(sb.getReceive_date())) {
                                cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                                cell[rowindex++].setCellValue(sb.getReceive_date_2());

                            } else {
                                cell[rowindex++].setCellValue("wait sld");
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getDoc_ready_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getDoc_ready_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSld_gap());
                            cell[rowindex++].setCellValue(""+sb.getSlider_contribution());

                            cell[rowindex++].setCellValue(""+sb.getDocDelay());

                            cell[rowindex++].setCellValue(sb.getLocation());



                            //cell[rowindex++].setCellValue(sb.getHga_act_issue_dt());
                            cell[rowindex++].setCellValue(sb.getHgaqueue());




                            indexData++;
                        }//end while
                        if (sliderList.size() > 0) {
                            indexData = indexData + 2;
                        }
                    } if (countdata == 0) {
                            
                            row = sheet[ln].createRow((short) indexData);
                            for (int indexCell = 0; indexCell < 28; indexCell++) {
                                cell[indexCell] = row.createCell((short) indexCell);
                                cell[indexCell].setCellStyle(style);
                                sheet[ln].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                            }
                            /* Set value of cells for Row[0] */ int rowindex = 0;
                            cell[rowindex++].setCellValue("EXP ID");
                            cell[rowindex++].setCellValue("PRODUCT");
                            cell[rowindex++].setCellValue("ET TSR");
                            cell[rowindex++].setCellValue("QTY");
                            cell[rowindex++].setCellValue("Slider Seq.");
                            cell[rowindex++].setCellValue("Wafer LOT");
                            cell[rowindex++].setCellValue("Priority");
                            cell[rowindex++].setCellValue("HGA BO");

                            cell[rowindex++].setCellValue("Retest Bo");
                            cell[rowindex++].setCellValue("Status");


                            cell[rowindex++].setCellValue("Wafer Ship Date");
                            cell[rowindex++].setCellValue("Sld Material Form");
                            cell[rowindex++].setCellValue("Sld Bo Priority");
                            cell[rowindex++].setCellValue("Slider Document Ready Date");
                            cell[rowindex++].setCellValue("Sld Start Date");
                            cell[rowindex++].setCellValue("Wafer partner gap (day)");

                            cell[rowindex++].setCellValue("HGA Material Form");





                            cell[rowindex++].setCellValue("Forecast Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Date");
                            cell[rowindex++].setCellValue("Slider Ship Destination");
                            cell[rowindex++].setCellValue("Slider Receive");



                            cell[rowindex++].setCellValue("Document Ready Date");
                            cell[rowindex++].setCellValue("Sld partner gap(day)");
                            cell[rowindex++].setCellValue("Sld Contribution(day)");
                            cell[rowindex++].setCellValue("Document Delay");
                            cell[rowindex++].setCellValue("Location");

                            // cell[rowindex++].setCellValue("Date To Wip");
                            cell[rowindex++].setCellValue("HGA Queue");









                            indexData++;
                        }
                }
            }


        } finally {
            //   out.close();
        }

        return wb;
    }

    public HSSFWorkbook BinExcelReportNotCmp(String binId) {
        String DATE_FORMAT = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
        Calendar c1 = Calendar.getInstance(); // today
        // response.setHeader("Content-Disposition", "attachment; filename=hgareport-"+sdf.format(c1.getTime())+".xls");
        //  OutputStream out = response.getOutputStream();
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
            SliderManager pc = new SliderManager();
            // Create a workbook

            // Create a sheet
            HSSFSheet[] sheet = new HSSFSheet[4];
            // Set style

            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.RED.index, (byte) 252, (byte) 157, (byte) 112);

            palette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 255, (byte) 255, (byte) 66);

            HSSFCellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            HSSFFont f2 = wb.createFont();
            //set font 2 to 10 point type
            f2.setFontHeightInPoints((short) 10);
            //make it red
            f2.setColor((short) 0);
            //make it bold
            f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            style.setFont(f2);

            HSSFCellStyle styleYello = wb.createCellStyle();
            styleYello.setFillForegroundColor(HSSFColor.YELLOW.index);
            styleYello.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleYello.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleYello.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle styleRed = wb.createCellStyle();
            styleRed.setFillForegroundColor(HSSFColor.RED.index);
            styleRed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleRed.setBorderTop(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderRight(HSSFCellStyle.BORDER_THIN);
            styleRed.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle normal = wb.createCellStyle();
            normal.setFillForegroundColor(HSSFColor.WHITE.index);
            normal.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            normal.setBorderTop(HSSFCellStyle.BORDER_THIN);
            normal.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            normal.setBorderRight(HSSFCellStyle.BORDER_THIN);
            normal.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            HSSFCellStyle cs = wb.createCellStyle();
            HSSFDataFormat df = wb.createDataFormat();
            cs.setDataFormat(df.getFormat("d-mmm-yy h:mm"));





            if (true) {
                // Create Row[TDT]
                HSSFRow row = null;
                sheet[0] = wb.createSheet("BIN Report");
                sheet[0].groupColumn((short) 10, (short) 14);
                sheet[0].groupColumn((short) 19, (short) 21);
                sheet[0].groupColumn((short) 23, (short) 24);

                //               row = sheet[ln].createRow((short) 0);


                // Create cells (cell[rowindex++] - CELL[n])and set style for Row[0]
                HSSFCell cell[] = new HSSFCell[28];



                SliderManager hga = new SliderManager();
                int indexData = 0;


                List sliderList = pc.loadSilderByBin("" + binId);

                row = sheet[0].createRow((short) indexData);
                for (int indexCell = 0; indexCell < 28; indexCell++) {
                    cell[indexCell] = row.createCell((short) indexCell);
                    cell[indexCell].setCellStyle(style);
                    sheet[0].setColumnWidth((short) (indexCell), (short) ((25 * 8) / ((double) 1 / 20)));
                }
                /* Set value of cells for Row[0] */
                int rowindex = 0;
                cell[rowindex++].setCellValue("EXP ID");
                cell[rowindex++].setCellValue("PRODUCT");
                cell[rowindex++].setCellValue("ET TSR");
                cell[rowindex++].setCellValue("QTY");
                cell[rowindex++].setCellValue("Slider Seq.");
                cell[rowindex++].setCellValue("Wafer LOT");
                cell[rowindex++].setCellValue("Priority");
                cell[rowindex++].setCellValue("HGA BO");

                cell[rowindex++].setCellValue("Retest Bo");
                cell[rowindex++].setCellValue("Status");


                cell[rowindex++].setCellValue("Wafer Ship Date");
                cell[rowindex++].setCellValue("Sld Material Form");
                cell[rowindex++].setCellValue("Sld Bo Priority");
                cell[rowindex++].setCellValue("Slider Document Ready Date");
                cell[rowindex++].setCellValue("Sld Start Date");
                cell[rowindex++].setCellValue("Wafer partner gap (day)");

                cell[rowindex++].setCellValue("HGA Material Form");





                cell[rowindex++].setCellValue("Forecast Ship Date");
                cell[rowindex++].setCellValue("Slider Ship Date");
                cell[rowindex++].setCellValue("Slider Ship Destination");
                cell[rowindex++].setCellValue("Slider Receive");



                cell[rowindex++].setCellValue("Document Ready Date");
                cell[rowindex++].setCellValue("Sld partner gap(day)");
                cell[rowindex++].setCellValue("Sld Contribution(day)");
                cell[rowindex++].setCellValue("Document Delay");
                cell[rowindex++].setCellValue("Location");

                // cell[rowindex++].setCellValue("Date To Wip");
                cell[rowindex++].setCellValue("HGA Queue");
                indexData++;

                for (int v = 0; v < sliderList.size(); v++) {

                    SliderBean sb = (SliderBean) sliderList.get(v);
                    row = sheet[0].createRow((short) indexData);
                    for (int indexCell = 0; indexCell < 28; indexCell++) {
                        cell[indexCell] = row.createCell((short) indexCell);
                        double gap = sb.getSlider_contribution();
                        if (gap >= 2 && gap <= 3) {
                            cell[indexCell].setCellStyle(styleYello);
                        } else if (gap > 3) {
                            cell[indexCell].setCellStyle(styleRed);
                        } else {
                            cell[indexCell].setCellStyle(normal);
                        }
                    }
                    // Set value of cells for Row[1] - Row[n]
                    rowindex = 0;
                    cell[rowindex++].setCellValue(sb.getBin());
                    cell[rowindex++].setCellValue(sb.getProductName());
                    cell[rowindex++].setCellValue(sb.getTsr());

                    cell[rowindex++].setCellValue(sb.getSld_cmp_qty());
                    cell[rowindex++].setCellValue(sb.getSeq());
                    cell[rowindex++].setCellValue(sb.getWaf_lot());
                    cell[rowindex++].setCellValue(sb.getPriority());
                    cell[rowindex++].setCellValue(sb.getHga_bo());

                    cell[rowindex++].setCellValue(sb.getRetest_bo());

                    int tempcell = rowindex++;
                    if ("Withdrawn".equalsIgnoreCase(sb.getHga_status()) || "Inactive".equalsIgnoreCase(sb.getHga_status())) {

                        cell[tempcell].setCellValue("Cancel");
                    } else {
                        if (sb.getHga_cmp_date() != null) {
                            cell[tempcell].setCellValue("Complete");
                            if ("FB".equalsIgnoreCase(sb.getBuildgroup())) {
                                cell[tempcell].setCellValue("Weekly Rebuild");
                            }
                        } else {
                            if (sb.getHga_bo() != null && sb.getHga_bo().startsWith("TH") && "STTH".equalsIgnoreCase(sb.getLocation()) && sb.getTsr() != null) {
                                cell[tempcell].setCellValue("Ready");
                            } else {
                                cell[tempcell].setCellValue("Not Ready");
                            }
                        }
                    }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getWaf_ship_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getWaf_ship_date_2());
                            }

                            cell[rowindex++].setCellValue(sb.getSld_material_form());
                            cell[rowindex++].setCellValue(sb.getSld_bo_priority());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSld_bo_first_approval_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSld_bo_first_approval_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getSlider_start_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getSlider_start_date_2());
                            }



                            cell[rowindex++].setCellValue(""+sb.getWafgap());
                            cell[rowindex++].setCellValue(sb.getMaterial_form());
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getForcast_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getForcast_date_2());
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getShip_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getShip_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSlider_ship_destination());
                            
                            if (sb.getReceive_date() != null || "".equalsIgnoreCase(sb.getReceive_date())) {
                                cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                                cell[rowindex++].setCellValue(sb.getReceive_date_2());

                            } else {
                                cell[rowindex++].setCellValue("wait sld");
                            }
                            cell[rowindex].getCellStyle().setDataFormat(df.getFormat("d-mmm-yy h:mm"));
                            if (sb.getDoc_ready_date_2() == null) {
                                cell[rowindex++].setCellValue("");
                            } else {
                                cell[rowindex++].setCellValue(sb.getDoc_ready_date_2());
                            }
                            cell[rowindex++].setCellValue(sb.getSld_gap());
                            cell[rowindex++].setCellValue(""+sb.getSlider_contribution());

                            cell[rowindex++].setCellValue(""+sb.getDocDelay());

                            cell[rowindex++].setCellValue(sb.getLocation());



                            //cell[rowindex++].setCellValue(sb.getHga_act_issue_dt());
                            cell[rowindex++].setCellValue(sb.getHgaqueue());




                    indexData++;
                }//end while
                if (sliderList.size() > 0) {
                    indexData = indexData + 2;
                }
            }







        } finally {
            //   out.close();
        }

        return wb;
    }
}
